<html>

<head>
<title>SQL Relay - Getting Started With SQLite</title>
<link rel="stylesheet" href="../css/styles.css">
</head>

<body>

<span class="heading1">Getting Started With SQLite</span><br>

<ul>
<li><a href="#installation">Installation</a></li>
<li><a href="#creating">Creating a Database</a></li>
<li><a href="#accessing">Accessing a Database</a></li>
<li><a href="#sqlrelay">Accessing a Database With SQL Relay</a></li>
</ul>

<a name="installation"></a>
<span class="heading1">Installation</span><br>

<p>I've successfully installed SQLite on Linux, FreeBSD, NetBSD, OpenBSD, SCO
OpenServer and Solaris.  On most platforms, I've had to compile it from 
source.  SQLite is available from the 
<a href="http://www.sqlite.org">SQLite home page</a>.  I usually give 
the <i>configure</i> script the <i>--prefix=/usr/local/sqlite</i> parameter so 
that SQLite will be installed entirely under /usr/local/sqlite and add 
/usr/local/sqlite/bin to my PATH environment variable and /usr/local/sqlite/lib
to my LD_LIBRARY_PATH environment variable.</p>

<p>On OpenBSD and NetBSD sqlite will build cleanly but will crash at run time.
To get it working, you have to edit the Makefile that the configure script 
generates, search for a line like:</p>

<blockquote>
<b>LIBREADLINE = -lreadline</b>
</blockquote>

<p>And add -lcurses to the end of it as such:</p>

<blockquote>
<b>LIBREADLINE = -lreadline -lcurses</b>
</blockquote>

<p>Compiling sqlite with this modification will make it work.</p>

<span class="heading2">RPM Based Linux</span><br>

<p>To install SQLite on an RPM-based Linux distribution like RedHat, 
Mandrake or TurboLinux, acquire the sqlite RPM from the
<a href="http://www.sqlite.org">SQLite home page</a> and install it
using <i>rpm -i</i>.</p>

<span class="heading2">Slackware Linux</span><br>

<p>The sqlite package is (or at least was once) available from 
<a href="http://www.infa.abo.fi/~patrik/slackpacks/">http://www.infa.abo.fi/~patrik/slackpacks/</a>.  You can install it using <i>installpkg</i>.</p>

<a name="creating"></a>
<span class="heading1">Creating a Database</span><br>

<p>In version 1.0.x of SQLite, a database is just a directory with table files 
in it.  In versions 2.x an 3.x, the database is a single file.  Unlike other
relation database systems, there is no daemon managing the database, the SQLite
API interprets queries and runs them against the file(s) directly. I usually
create a directory /usr/local/sqlite/var and create database directories or
files there as follows.  SQLite database users are equivalent to unix users.  
Database directory and file permissions dictate what permissions a user has on 
the database.</p>

<blockquote>
<b>mkdir -p /usr/local/sqlite/var</b><br>
<b>chown testuser /usr/local/sqlite/var</b><br>
<b>chmod 755 /usr/local/sqlite/var</b><br>
</blockquote>

<p>For SQLite versions 2.x and 3.x, it is only necessary to create the
directory, the database file will get created the first time a user tries to
access it.</p>

<p>For SQLite version 1.0.x, the following commands are necessary to create a 
database called <i>testdb</i> owned by the user <i>testuser</i>.</p>

<blockquote>
<b>mkdir -p /usr/local/sqlite/var/testdb</b><br>
<b>chown testuser /usr/local/sqlite/var/testdb</b><br>
<b>chmod 755 /usr/local/sqlite/var/testdb</b><br>
</blockquote>

<a name="accessing"></a>
<span class="heading1">Accessing a Database</span><br>

<p>To access an SQLite database, su to the appropriate user and run the
sqlite3 command line utility (for versions 1.0.x and 2.x the command line
utility is just called sqlite).</p>

<blockquote>
<b>su testuser</b><br>
<b>sqlite3 /usr/local/sqlite/var/testdb</b>
</blockquote>

<p>Once you're connected to the database, the sqlite client prompts you to
enter a query.  Queries may be split across multiple lines.  To run a query, 
end it with a semicolon.  To exit, type .exit</p>

<p>A sample sqlite session follows.</p>

<blockquote>
<PRE>
[testuser@localhost testuser]$ <b>sqlite3 /usr/local/sqlite/var/testdb</b>
SQLite version <FONT color=#ff00ff>3.0.2</FONT>
Enter <FONT color=#ff00ff>&quot;.help&quot;</FONT> for instructions
sqlite&gt; <B><FONT color=#a62828>create</FONT></B> <FONT color=#6959cf>table</FONT> testtable (
   ...&gt; col1 <B><FONT color=#288a51>char</FONT></B>(<FONT color=#ff00ff>40</FONT>),
   ...&gt; col2 <B><FONT color=#288a51>integer</FONT></B>
   ...&gt; );
sqlite&gt; .tables
testtable
sqlite&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'hello'</FONT>,<FONT color=#ff00ff>50</FONT>);
sqlite&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'hi'</FONT>,<FONT color=#ff00ff>60</FONT>);
sqlite&gt; <B><FONT color=#a62828>insert</FONT></B> <FONT color=#6959cf>into</FONT> testtable <FONT color=#6959cf>values</FONT> (<FONT color=#ff00ff>'bye'</FONT>,<FONT color=#ff00ff>70</FONT>);
sqlite&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable;
hi|<FONT color=#ff00ff>60</FONT>
hello|<FONT color=#ff00ff>50</FONT>
bye|<FONT color=#ff00ff>70</FONT>
sqlite&gt; <B><FONT color=#a62828>update</FONT></B> testtable <B><FONT color=#a62828>set</FONT></B> col2=<FONT color=#ff00ff>0</FONT> <FONT color=#6959cf>where</FONT> col1=<FONT color=#ff00ff>'hi'</FONT>;
sqlite&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable;
hi|<FONT color=#ff00ff>0</FONT>
hello|<FONT color=#ff00ff>50</FONT>
bye|<FONT color=#ff00ff>70</FONT>
sqlite&gt; <B><FONT color=#a62828>delete</FONT></B> <FONT color=#6959cf>from</FONT> testtable <FONT color=#6959cf>where</FONT> col2=<FONT color=#ff00ff>50</FONT>;
sqlite&gt; <B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> testtable;
hi|<FONT color=#ff00ff>0</FONT>
bye|<FONT color=#ff00ff>70</FONT>
sqlite&gt; <B><FONT color=#a62828>drop</FONT></B> <FONT color=#6959cf>table</FONT> testtable;
sqlite&gt; .exit
</PRE>
</blockquote>

<a name="sqlrelay"></a>
<span class="heading1">Accessing a Database With SQL Relay</span><br>

<p>Accessing SQLite from SQL Relay requires an instance entry in your 
<i>sqlrelay.conf</i> file for the database that you want 
to access.  Here is an example <i>sqlrelay.conf</i> which defines an SQL Relay 
instance called sqlitetest.  This instance connects to the 
<i>/usr/local/sqlite/var/testdb</i> database.  Note how the runasuser and 
runasgroup attributes of the instance tag are set to the owner of the 
database.</p>

<PRE>
<FONT color=#0000ff>&lt;?</FONT><B><FONT color=#288a51>xml version=</FONT></B><FONT color=#ff00ff>&quot;1.0&quot;</FONT><FONT color=#0000ff>?&gt;</FONT>
<FONT color=#008a8e>&lt;!</FONT><B><FONT color=#a62828>DOCTYPE</FONT></B> instances <B><FONT color=#a62828>SYSTEM</FONT></B> <FONT color=#ff00ff>&quot;sqlrelay.dtd&quot;</FONT><FONT color=#008a8e>&gt;</FONT>
<FONT color=#008a8e>&lt;instances&gt;</FONT>

        <FONT color=#008a8e>&lt;instance id=</FONT><FONT color=#ff00ff>&quot;sqlitetest&quot;</FONT><FONT color=#008a8e> port=</FONT><FONT color=#ff00ff>&quot;9000&quot;</FONT><FONT color=#008a8e> socket=</FONT><FONT color=#ff00ff>&quot;/tmp/sqlitetest.socket&quot;</FONT><FONT color=#008a8e> dbase=</FONT><FONT color=#ff00ff>&quot;sqlite&quot;</FONT><FONT color=#008a8e> connections=</FONT><FONT color=#ff00ff>&quot;3&quot;</FONT><FONT color=#008a8e> maxconnections=</FONT><FONT color=#ff00ff>&quot;5&quot;</FONT><FONT color=#008a8e> maxqueuelength=</FONT><FONT color=#ff00ff>&quot;0&quot;</FONT><FONT color=#008a8e> growby=</FONT><FONT color=#ff00ff>&quot;1&quot;</FONT><FONT color=#008a8e> ttl=</FONT><FONT color=#ff00ff>&quot;60&quot;</FONT><FONT color=#008a8e> endofsession=</FONT><FONT color=#ff00ff>&quot;commit&quot;</FONT><FONT color=#008a8e> sessiontimeout=</FONT><FONT color=#ff00ff>&quot;600&quot;</FONT><FONT color=#008a8e> runasuser=</FONT><FONT color=#ff00ff>&quot;testuser&quot;</FONT><FONT color=#008a8e> runasgroup=</FONT><FONT color=#ff00ff>&quot;testuser&quot;</FONT><FONT color=#008a8e> cursors=</FONT><FONT color=#ff00ff>&quot;5&quot;</FONT><FONT color=#008a8e> authtier=</FONT><FONT color=#ff00ff>&quot;listener&quot;</FONT><FONT color=#008a8e> handoff=</FONT><FONT color=#ff00ff>&quot;pass&quot;</FONT><FONT color=#008a8e>&gt;</FONT>
                <FONT color=#008a8e>&lt;users&gt;</FONT>
                        <FONT color=#008a8e>&lt;user user=</FONT><FONT color=#ff00ff>&quot;sqlitetest&quot;</FONT><FONT color=#008a8e> password=</FONT><FONT color=#ff00ff>&quot;sqlitetest&quot;</FONT><FONT color=#008a8e>/&gt;</FONT>
                <FONT color=#008a8e>&lt;/users&gt;</FONT>
                <FONT color=#008a8e>&lt;connections&gt;</FONT>
                        <FONT color=#008a8e>&lt;connection connectionid=</FONT><FONT color=#ff00ff>&quot;sqlitetest&quot;</FONT><FONT color=#008a8e> string=</FONT><FONT color=#ff00ff>&quot;db=/usr/local/sqlite/var/testdb&quot;</FONT><FONT color=#008a8e> metric=</FONT><FONT color=#ff00ff>&quot;1&quot;</FONT><FONT color=#008a8e>/&gt;</FONT>
                <FONT color=#008a8e>&lt;/connections&gt;</FONT>
        <FONT color=#008a8e>&lt;/instance&gt;</FONT>

<FONT color=#008a8e>&lt;/instances&gt;</FONT>
</PRE>

<p>Now you can start up this instance with the following command.</p>

<blockquote>
<b>sqlr-start -id sqlitetest</b>
</blockquote>

<p>To connect to the instance and run queries, use the following command.</p>

<blockquote>
<b>sqlrsh -id sqlitetest</b>
</blockquote>

<p>The following command shuts down the SQL Relay instance.</p>

<blockquote>
<b>sqlr-stop sqlitetest</b>
</blockquote>

</body>

</html>
